
import numpy as np
import pandas as pd
from os import walk
import os
import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignment


class Homework_count:
    def __init__(self,order):
        self.order=order
        self.data = pd.read_csv('选课名单/0ACourse_list_new2.csv',encoding='gb18030')
        #读取选课列表中的学生学号
        self.CourseList_ID1 = np.array(self.data.iloc[:, 0:2])
        self.CourseList_ID = np.insert(self.CourseList_ID1, 2, axis=1, values=0)

        # folder = os.getcwd() + '\\homework\\作业{order}\\'.format(order=self.order)
        folder =  os.getcwd() + '/homework/作业{}/'.format(self.order)
        # 获取此py文件路径，在此路径选创建在new_folder文件夹中的test文件夹)
        if not os.path.exists(folder):
            os.makedirs(folder)
        if not os.path.exists(folder+'有错误的同学/'):
            os.makedirs(folder+'有错误的同学/')

    def Read_homeworkID(self):    #用于收到读取作业文件名中的学生学号
        filePath = "../作业{order}/".format(order=self.order)
        _, _, names = next(walk(filePath))
        for i in range(len(names)):
            names[i]=int(names[i][0:7])
        # pd.DataFrame(np.array(names)).to_csv('已交作业.csv', index=True, encoding="utf_8_sig")
        return np.array(names).reshape(len(names),1)
    def count(self):  #比对选课名单与收到作业文件名中的学生学号

        Student_ID=self.CourseList_ID
        Homework_ID = self.Read_homeworkID()
        print('Homework_ID',Homework_ID.shape)
        Homework_ID = np.insert(Homework_ID, 1, axis=1, values=0)
        for i in range(len(Student_ID)):
            for j in range(len(Homework_ID)):
                if Student_ID[i][0]== Homework_ID[j][0]:

                    Student_ID[i][2]=1
                    Homework_ID[j][1]=Homework_ID[j][1]+1
                    break

        #print(Student_ID)
        return Student_ID, Homework_ID
    def print_NOfinish(self):
        Student_ID, Homework_ID = self.count()
        Student_ID_NOfinish = []
        Student_ID_uncount = []
        for i in range(len(Student_ID)):
            if Student_ID[i][2] == 0:
                Student_ID_NOfinish.append(Student_ID[i])
        self.output(np.array(Student_ID_NOfinish),  'homework/作业{order}/第{order}次作业-未交统计.csv'.format(order=self.order))
        # self.save_excel(np.array(Student_ID_NOfinish),'作业{order}/第{order}次作业-未交统计.xlsx'.format(order=self.order))
        #已交作业未统计
        for i in range(len(Homework_ID)):
            if Homework_ID[i][1] == 0:
                Student_ID_uncount.append(Homework_ID[i])
        if len(Student_ID_uncount)!=0:
            pd.DataFrame({"学号":np.array(Student_ID_uncount)[:,0]}).to_csv( 'homework/作业{order}/第{order}次作业-已交未选课统计.csv'.format(order=self.order),encoding="utf_8_sig")
        return 0
    def output(self,jilu,string):
        submission = pd.DataFrame({"学号": jilu[:, 0], "姓名": jilu[:, 1], "完成": jilu[:, 2]})
        submission.to_csv(string, index=True,encoding="utf_8_sig")
        return 0
    def save_excel(self,jilu):
        """
        保存汇总数据
        :param jilu:
        :param name:
        :return:
        """
        fileName='homework/作业{i}/第3-{i}次作业情况汇总.xlsx'.format(i=self.order)
        columns=["学号", "姓名"]
        for i in range(jilu.shape[1]-3):
            columns.append('第{order}次'.format(order=i+3))
        columns.append('共完成/次')
        submission = pd.DataFrame(jilu, columns=columns)
        submission.to_excel(fileName, index=True, encoding="utf_8_sig",sheet_name='统计汇总')

        columnOfResult=jilu.shape[1]+1
        print (columnOfResult)
        wb = openpyxl.load_workbook(fileName)
        ws=wb['统计汇总']
        fill_red = PatternFill("solid", fgColor="ff0000")
        fill_green = PatternFill("solid", fgColor="00FF00")
        fill_yellow = PatternFill("solid", fgColor="FFFF00")
        font_set = Font(name='宋体', size=12, italic=False, color=colors.BLACK, bold=True)
        for i in range(len(jilu)):
            for j in range(jilu.shape[1]):
                ws.cell(row=2 + i, column=2+j).alignment = Alignment(horizontal='center', vertical='center')
                if ws.cell(row=2 + i, column=2+j).value==0:
                    ws.cell(row=2 + i, column=2 + j).fill= \
                        PatternFill("solid", fgColor="BBFFFF")
        for i in range(len(jilu)):
            ws.cell(row=2 + i, column=columnOfResult).font = font_set
            if ws.cell(row=2 + i, column=columnOfResult).value==(jilu.shape[1]-3):
                ws.cell(row=2 + i, column=columnOfResult).fill = fill_green
            elif ws.cell(row=2 + i, column=columnOfResult).value<(jilu.shape[1]-3)/2:
                ws.cell(row=2 + i, column=columnOfResult).fill = fill_red
            else:
                ws.cell(row=2 + i, column=columnOfResult).fill = fill_yellow
        wb.save(fileName)

    def save_newsheet(self, jilu, name):
        wb = openpyxl.load_workbook('ceshi.xlsx')
        ws1 = wb.create_sheet(title='Sheet7', index=0)
        font_set = Font(name='宋体', size=11, italic=False, color=colors.BLACK, bold=True)
        ws1.cell(1, 2, '学号').font = font_set
        ws1.cell(1, 3, '姓名').font = font_set
        data = np.arange(10).reshape(5, 2)
        # ws1.alignment=Alignment(horizontal='center', vertical='center')
        for i in range(len(data)):
            ws1.cell(i + 2, 2, data[i][0])
            ws1.cell(i + 2, 3, data[i][1])
        wb.save('ceshi.xlsx')
    def sum_finish(self):
        """
        汇总多次作业结果
        :param orders: 单词未交csv文件
        :return:  矩阵，包含姓名学号每次交作业情况
        """
        orders = self.order + 1
        CourseList=self.CourseList_ID1
        for i in range(3,orders):
            data = pd.read_csv('homework/作业{i}/第{i}次作业-未交统计.csv'.format(i=i))
            # 读取选课列表中的学生学号
            weijiao= np.array(data.iloc[:, 1:2])
            CourseList=self.count_one( CourseList,  weijiao)
        sum= CourseList[:,2:].sum(1).reshape(len(CourseList),1)
        return np.concatenate((CourseList,sum),axis=1)
        # print (self.CourseList_ID)

    def count_one(self,Student_ID,Homework_ID):  # 比对选课名单与收到作业文件名中的学生学号
        """
        :param Student_ID: 选课名单上学号列表
        :param Homework_ID: 单次未交作业名单上学号列表
        :return:在Student_ID后加一列，完成1，未成0
        """
        Student_ID = Student_ID
        Homework_ID = Homework_ID
        result=np.ones(len(Student_ID))
        Homework_ID = np.insert(Homework_ID, 1, axis=1, values=1)
        for i in range(len(Student_ID)):
            for j in range(len(Homework_ID)):
                if Student_ID[i][0] == Homework_ID[j][0]:
                    result[i] = 0
                    break
        return np.concatenate((Student_ID,result.reshape(len(result),1)),axis=1)
if __name__ == "__main__":
    Homework=Homework_count(9)
    Homework.print_NOfinish()
    result=Homework.sum_finish()
    # # print (result)
    Homework.save_excel(result)


